package com.guigu.qingcheng.mapper;

import com.guigu.qingcheng.pojo.TbOrderItem;
import com.guigu.qingcheng.pojo.dto.CaiFenDinDan;
import org.apache.ibatis.annotations.Param;
import com.guigu.qingcheng.pojo.dto.CategoryReportDto;
import com.guigu.qingcheng.pojo.vo.CategoryReportVo;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import tk.mybatis.mapper.common.Mapper;

import java.util.List;

import java.time.LocalDate;
import java.util.List;
import java.util.Map;

public interface TbOrderItemMapper extends Mapper<TbOrderItem> {


     List<CaiFenDinDan> queryByspId(@Param("id") String id);

    /**
     * 查询销售总额
     * @param localDate
     * @return
     */
    Map findTotalSales(@Param("localDate") LocalDate localDate);
    /**
     * 查询近七天销售总额
     * @param categoryReportVo
     * @return
     */
    Map findTotalSalesonweek(@Param("categoryReportVo") CategoryReportVo categoryReportVo);

    /**
     * 查询近七天销售商品数量，排序
     * @param
     * @return
     */
    @Select("SELECT oi.* FROM  qingcheng_order.`tb_order` o INNER JOIN qingcheng_order.`tb_order_item` oi \n" +
            "ON o.id=oi.order_id   INNER JOIN qingcheng_goods.`tb_spu` spu ON  spu.id=oi.spu_id\n" +
            "WHERE o.create_time>='2019-05-22' AND o.create_time<='2019-05-30'  \n" +
            "AND pay_status='1' \n" +
            "AND is_marketable='1'\n" +
            "GROUP BY oi.order_id \n" +
            "ORDER BY SUM(oi.num)   DESC LIMIT 4")
    List<Map> hostCommodities();

    /**
     * 查询特价商品
     * @return
     */
    @Select("SELECT oi.* FROM  qingcheng_order.`tb_order` o INNER JOIN qingcheng_order.`tb_order_item` oi \n" +
            "ON o.id=oi.order_id   INNER JOIN qingcheng_goods.`tb_spu` spu ON  spu.id=oi.spu_id\n" +
            "WHERE o.create_time>='2019-05-25' AND o.create_time<='2019-05-26'  \n" +
            "AND o.pay_status='1' \n" +
            "AND spu.is_marketable='1'\n" +
            "GROUP BY oi.order_id \n" +
            "ORDER BY price  DESC LIMIT 4")
        List<Map> lessPrice();

    /**
     * 新品上架
     * @return
     */
    @Select("SELECT * FROM  qingcheng_order.`tb_order` o INNER JOIN qingcheng_order.`tb_order_item` oi \n" +
            "ON o.id=oi.order_id   INNER JOIN qingcheng_goods.`tb_spu` spu ON  spu.id=oi.spu_id\n" +
            "WHERE o.pay_status='1' \n" +
            "AND spu.is_marketable='1'\n" +
            "GROUP BY oi.order_id \n" +
            "ORDER BY o.create_time ASC LIMIT 4")
    List<Map> newCommodities();

}